import java.sql.*;

public class DatabaseSetup {
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/?useUnicode=true&characterEncoding=UTF8&serverTimezone=PRC&useSSL=false&allowMultiQueries=true&allowPublicKeyRetrieval=true";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "12345678";
    
    public static void main(String[] args) {
        try {
            // 加载MySQL驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // 连接数据库
            System.out.println("正在连接MySQL数据库...");
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            Statement stmt = conn.createStatement();
            
            System.out.println("✓ 数据库连接成功！");
            
            // 创建数据库
            System.out.println("正在创建数据库 wvp...");
            stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS wvp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
            stmt.executeUpdate("USE wvp");
            System.out.println("✓ 数据库 wvp 创建成功！");
            
            // 检查现有表
            System.out.println("检查现有表...");
            ResultSet rs = stmt.executeQuery("SHOW TABLES");
            boolean hasExistingTables = false;
            while (rs.next()) {
                System.out.println("  - " + rs.getString(1));
                hasExistingTables = true;
            }
            if (!hasExistingTables) {
                System.out.println("  没有找到现有表");
            }
            rs.close();
            
            // 创建核心表
            System.out.println("正在创建核心表...");
            
            // 1. 用户角色表
            createUserRoleTable(stmt);
            
            // 2. 用户表
            createUserTable(stmt);
            
            // 3. 媒体服务器表
            createMediaServerTable(stmt);
            
            // 4. 平台表
            createPlatformTable(stmt);
            
            // 5. 设备表
            createDeviceTable(stmt);
            
            // 6. 设备通道表
            createDeviceChannelTable(stmt);
            
            // 7. 录像计划表
            createRecordPlanTable(stmt);
            
            // 8. 录像计划项表
            createRecordPlanItemTable(stmt);
            
            // 插入默认数据
            System.out.println("正在插入默认数据...");
            insertDefaultData(stmt);
            
            // 验证表创建
            System.out.println("验证表创建结果...");
            rs = stmt.executeQuery("SHOW TABLES");
            System.out.println("创建的表：");
            while (rs.next()) {
                System.out.println("  ✓ " + rs.getString(1));
            }
            rs.close();
            
            stmt.close();
            conn.close();
            
            System.out.println("\n🎉 数据库初始化完成！");
            System.out.println("默认登录信息：");
            System.out.println("  用户名: admin");
            System.out.println("  密码: admin");
            System.out.println("现在可以启动WVP应用程序了！");
            
        } catch (Exception e) {
            System.err.println("❌ 数据库初始化失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    private static void createUserRoleTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS wvp_user_role (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "name VARCHAR(255) NOT NULL," +
                "authority VARCHAR(255)," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)" +
                ")";
        stmt.executeUpdate(sql);
        System.out.println("  ✓ wvp_user_role 表创建成功");
    }
    
    private static void createUserTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS wvp_user (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "username VARCHAR(255) NOT NULL UNIQUE," +
                "password VARCHAR(255) NOT NULL," +
                "role_id INT DEFAULT 1," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)," +
                "push_key VARCHAR(50)" +
                ")";
        stmt.executeUpdate(sql);
        System.out.println("  ✓ wvp_user 表创建成功");
    }
    
    private static void createMediaServerTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS wvp_media_server (" +
                "id VARCHAR(255) PRIMARY KEY," +
                "ip VARCHAR(50) NOT NULL," +
                "hook_ip VARCHAR(50)," +
                "sdp_ip VARCHAR(50)," +
                "stream_ip VARCHAR(50)," +
                "http_port INT," +
                "http_ssl_port INT," +
                "rtmp_port INT," +
                "rtmp_ssl_port INT," +
                "rtp_proxy_port INT," +
                "rtsp_port INT," +
                "rtsp_ssl_port INT," +
                "auto_config TINYINT(1) DEFAULT 1," +
                "secret VARCHAR(50)," +
                "rtp_enable TINYINT(1) DEFAULT 0," +
                "rtp_port_range VARCHAR(50)," +
                "record_assist_port INT," +
                "default_server TINYINT(1) DEFAULT 0," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)," +
                "last_keepalive_time VARCHAR(50)," +
                "status TINYINT(1) DEFAULT 0," +
                "send_rtp_port_range VARCHAR(50)," +
                "record_day INT DEFAULT 7," +
                "server_id VARCHAR(50)" +
                ")";
        stmt.executeUpdate(sql);
        System.out.println("  ✓ wvp_media_server 表创建成功");
    }
    
    private static void createPlatformTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS wvp_platform (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "enable TINYINT(1) DEFAULT 0," +
                "name VARCHAR(255)," +
                "server_gb_id VARCHAR(50)," +
                "server_gb_domain VARCHAR(50)," +
                "server_ip VARCHAR(50)," +
                "server_port INT," +
                "device_gb_id VARCHAR(50)," +
                "device_ip VARCHAR(50)," +
                "device_port VARCHAR(50)," +
                "username VARCHAR(255)," +
                "password VARCHAR(255)," +
                "expires VARCHAR(50)," +
                "keep_timeout VARCHAR(50)," +
                "transport VARCHAR(50)," +
                "character_set VARCHAR(50)," +
                "catalog_id VARCHAR(50)," +
                "ptz TINYINT(1) DEFAULT 0," +
                "rtcp TINYINT(1) DEFAULT 0," +
                "status TINYINT(1) DEFAULT 0," +
                "start_offline_push TINYINT(1) DEFAULT 0," +
                "catalog_group INT DEFAULT 1," +
                "administrative_division VARCHAR(50)," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)," +
                "as_message_channel TINYINT(1) DEFAULT 0," +
                "server_id VARCHAR(50)" +
                ")";
        stmt.executeUpdate(sql);
        System.out.println("  ✓ wvp_platform 表创建成功");
    }
    
    private static void createDeviceTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS wvp_device (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "device_id VARCHAR(50) NOT NULL UNIQUE," +
                "name VARCHAR(255)," +
                "manufacturer VARCHAR(255)," +
                "model VARCHAR(255)," +
                "firmware VARCHAR(255)," +
                "transport VARCHAR(50)," +
                "stream_mode VARCHAR(50)," +
                "on_line TINYINT(1) DEFAULT 0," +
                "register_time VARCHAR(50)," +
                "keepalive_time VARCHAR(50)," +
                "ip VARCHAR(50)," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)," +
                "port INT," +
                "expires INT," +
                "host_address VARCHAR(50)," +
                "charset VARCHAR(50)," +
                "media_server_id VARCHAR(50) DEFAULT 'auto'," +
                "password VARCHAR(255)," +
                "server_id VARCHAR(50)" +
                ")";
        stmt.executeUpdate(sql);
        System.out.println("  ✓ wvp_device 表创建成功");
    }
    
    private static void createDeviceChannelTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS wvp_device_channel (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "channel_id VARCHAR(50) NOT NULL," +
                "device_id VARCHAR(50) NOT NULL," +
                "name VARCHAR(255)," +
                "manufacture VARCHAR(255)," +
                "model VARCHAR(255)," +
                "owner VARCHAR(255)," +
                "civil_code VARCHAR(255)," +
                "block VARCHAR(255)," +
                "address VARCHAR(255)," +
                "parent_id VARCHAR(50)," +
                "safety_way INT," +
                "register_way INT," +
                "cert_num VARCHAR(255)," +
                "certifiable INT," +
                "err_code INT," +
                "end_time VARCHAR(255)," +
                "secrecy VARCHAR(255)," +
                "ip_address VARCHAR(255)," +
                "port INT," +
                "password VARCHAR(255)," +
                "ptz_type INT," +
                "status TINYINT(1) DEFAULT 0," +
                "longitude DOUBLE," +
                "latitude DOUBLE," +
                "stream_id VARCHAR(255)," +
                "device_type VARCHAR(50)," +
                "channel_type INT," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)," +
                "sub_count INT DEFAULT 0," +
                "parental INT DEFAULT 0," +
                "has_audio TINYINT(1) DEFAULT 0," +
                "record_plan_id INT" +
                ")";
        stmt.executeUpdate(sql);
        System.out.println("  ✓ wvp_device_channel 表创建成功");
    }
    
    private static void createRecordPlanTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS wvp_record_plan (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "snap TINYINT(1) DEFAULT 0," +
                "name VARCHAR(255) NOT NULL," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)" +
                ")";
        stmt.executeUpdate(sql);
        System.out.println("  ✓ wvp_record_plan 表创建成功");
    }
    
    private static void createRecordPlanItemTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS wvp_record_plan_item (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "start INT," +
                "stop INT," +
                "week_day INT," +
                "plan_id INT," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)" +
                ")";
        stmt.executeUpdate(sql);
        System.out.println("  ✓ wvp_record_plan_item 表创建成功");
    }
    
    private static void insertDefaultData(Statement stmt) throws SQLException {
        // 插入默认角色
        stmt.executeUpdate("INSERT IGNORE INTO wvp_user_role VALUES (1, 'admin', '0', '2021-04-13 14:14:57', '2021-04-13 14:14:57')");
        System.out.println("  ✓ 默认角色数据插入成功");
        
        // 插入默认用户 (admin/admin)
        stmt.executeUpdate("INSERT IGNORE INTO wvp_user VALUES (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 1, '2021-04-13 14:14:57', '2021-04-13 14:14:57', '3e80d1762a324d5b0ff636e0bd16f1e3')");
        System.out.println("  ✓ 默认用户数据插入成功");
    }
}
